package com.wanmait.cookbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.cookbook.vo.Cookbook;
import com.wanmait.cookbook.vo.CookbookComment;
import com.wanmait.cookbook.vo.Userinfo;
import com.wanmait.demo.util.DBHelper;

/**
 *食锟斤拷锟斤拷锟斤拷DAO
 * @author 17297
 *
 */
public class CookbookCommentDAO {
	private CookbookCommentDAO() {
	}

	private static CookbookCommentDAO cookbookCommentDAO = new CookbookCommentDAO();

	public static CookbookCommentDAO getCookbookCommentDAO() {
		return cookbookCommentDAO;
	}
	
	public List<Integer> findCookbookComment() {
		List<Integer> counts = new ArrayList<Integer>();
		
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		return counts;
	}
	  /**
	   *  鑾峰緱涓�绾ц瘎璁�
	   * @return
	   */
	  public ArrayList<CookbookComment> getFirstComments(Integer cookbookId) {
	    ArrayList<CookbookComment> allComments = new ArrayList<CookbookComment>();
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    String sql = "SELECT * FROM cookbook_comment,userinfo WHERE cookbook_comment.cookbook_id=? AND userinfo_id=userinfo.id AND parent_comment_id=0 and cookbook_comment.enable=1";
	    try {
	      conn = DBHelper.getHelper().getConn();
	      ps = conn.prepareStatement(sql);
	      ps.setInt(1, cookbookId);
	      rs = ps.executeQuery();
	      CookbookComment comment = null;
	      while (rs.next()) {
	        comment = new CookbookComment();
	        comment.setId(rs.getInt("cookbook_comment.id"));
	        Cookbook cookbook = new Cookbook();
	        cookbook.setId(rs.getInt("cookbook_comment.cookbook_id"));
	        comment.setCookbook(cookbook);
	        Userinfo userinfo = new Userinfo();
	        userinfo.setId(rs.getInt("userinfo.id"));
	        userinfo.setName(rs.getString("name"));
	        userinfo.setAvatar(rs.getString("avatar"));
	        comment.setUserinfo(userinfo);
	        Userinfo pU = new Userinfo();
	        pU.setId(0);
	        comment.setParentComment(pU);
	        comment.setContent(rs.getString("content"));
	        comment.setInputTime(rs.getTimestamp("cookbook_comment.input_time"));
	        
	        this.getChidrenComments(comment, cookbookId);
	        allComments.add(comment);
	      }
	    } catch (SQLException e) {
	      // TODO Auto-generated catch block
	      e.printStackTrace();
	    }
	    finally {
	      DBHelper.getHelper().closeConn(rs, ps, conn);
	    }
	    return allComments;
	  }
	  /**
	   * 娣诲姞璇勮
	   * @param comment
	   */
	  public void addFirstComment(CookbookComment comment) {
	    String sql = "INSERT INTO cookbook_comment(cookbook_id,userinfo_id,parent_comment_id,content) VALUES(?,?,?,?)";
	    DBHelper.getHelper().executeUpdate(sql, comment.getCookbook().getId(),comment.getUserinfo().getId(),comment.getParentComment().getId(),comment.getContent());
	  }

	  /**
	   *  鑾峰緱璇勮鐨勬墍鏈夊瓙璇勮
	   * @param parent
	   * @param cookbookId
	   * @return
	   */
	  public ArrayList<CookbookComment> getChidrenComments(CookbookComment parent,Integer cookbookId) {
	    ArrayList<CookbookComment> childrenComments = new ArrayList<CookbookComment>();
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    String sql = "SELECT * FROM cookbook_comment,userinfo WHERE cookbook_comment.cookbook_id=? AND userinfo_id=userinfo.id AND parent_comment_id=? and cookbook_comment.enable=1";
	    try {
	      conn = DBHelper.getHelper().getConn();
	      ps = conn.prepareStatement(sql);
	      ps.setInt(1, cookbookId);
	      ps.setInt(2, parent.getId());
	      rs = ps.executeQuery();
	      CookbookComment comment = null;
	      while (rs.next()) {
	        comment = new CookbookComment();
	        comment.setId(rs.getInt("cookbook_comment.id"));
	        Cookbook cookbook = new Cookbook();
	        cookbook.setId(cookbookId);
	        comment.setCookbook(cookbook);
	        Userinfo userinfo = new Userinfo();
	        userinfo.setId(rs.getInt("userinfo_id"));
	        userinfo.setName(rs.getString("name"));
	        userinfo.setAvatar(rs.getString("avatar"));
	        comment.setUserinfo(userinfo);
	        Userinfo uP = new Userinfo();
	        uP.setId(parent.getId());
	        comment.setParentComment(uP);
	        comment.setContent(rs.getString("content"));
	        comment.setInputTime(rs.getTimestamp("cookbook_comment.input_time"));
	        
	        childrenComments.add(comment);
	        comment.setParent(parent);
	        
	        this.getChidrenComments(comment, cookbookId); // 閫掑綊鑾峰緱鎵�鏈夊瓙璇勮
	      }
	      parent.setChildren(childrenComments);
	    } catch (SQLException e) {
	      // TODO Auto-generated catch block
	      e.printStackTrace();
	    }
	    finally {
	      DBHelper.getHelper().closeConn(rs, ps, conn);
	    }
	    return childrenComments;
	  }

	//锟斤拷锟斤拷食锟斤拷锟斤拷锟桔碉拷id锟斤拷锟绞筹拷锟斤拷锟斤拷锟�
	public CookbookComment getCookbookCommentByCommentId(int id) {
		CookbookComment cookbookComment = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql="SELECT * FROM cookbook_comment,userinfo "
				+ " WHERE cookbook_comment.id=? "
				+ " AND cookbook_comment.userinfo_id=userinfo.id";
		try {
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				cookbookComment= new CookbookComment();
				cookbookComment.setId(rs.getInt("cookbook_comment.id"));
				Userinfo userinfo = new Userinfo();
				userinfo.setId(rs.getInt("userinfo.id"));
				userinfo.setUsername(rs.getString("username"));
				cookbookComment.setUserinfo(userinfo);
				cookbookComment.setContent(rs.getString("content"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		return cookbookComment;
	}
	
	
	// 通锟斤拷ID删锟斤拷食锟斤拷锟斤拷锟斤拷
		public void deleteCookbookCommentByid(int id) {
			String sql = "update cookbook_comment set enable=0 where id=?";
			DBHelper.getHelper().executeUpdate(sql, id);
		}
		
		// 通锟斤拷IDs删锟斤拷锟斤拷锟绞筹拷锟斤拷锟斤拷锟�
		public void deleteCookbookCommentsByIds(String ids) {
			String sql = "update cookbook_comment set enable=0 where id in(" + ids + ")";
			DBHelper.getHelper().executeUpdate(sql);
		}
	
		//根据评论id查找用户
		public Userinfo getUserinfoByCommentId(int id) {
			Userinfo userinfo=null;
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql="SELECT * FROM userinfo WHERE id=(SELECT userinfo_id FROM cookbook_comment WHERE id=?)";
			try {
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs=ps.executeQuery();
				if(rs.next()) {
					userinfo = new Userinfo();
					userinfo.setId(rs.getInt("id"));
					userinfo.setName(rs.getString("name"));
					userinfo.setAvatar(rs.getString("avatar"));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return userinfo;
		}
		
		
		//根据食谱id查找用户
				public Userinfo getUserinfoByCookbookId(int id) {
					Userinfo userinfo=null;
					Connection conn = null;
					PreparedStatement ps = null;
					ResultSet rs = null;
					String sql="SELECT * FROM userinfo WHERE id=(SELECT userinfo_id FROM cookbook WHERE id=?)";
					try {
						conn=DBHelper.getHelper().getConn();
						ps=conn.prepareStatement(sql);
						ps.setInt(1, id);
						rs=ps.executeQuery();
						if(rs.next()) {
							userinfo = new Userinfo();
							userinfo.setId(rs.getInt("id"));
							userinfo.setName(rs.getString("name"));
							userinfo.setAvatar(rs.getString("avatar"));
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}finally {
						DBHelper.getHelper().closeConn(rs, ps, conn);
					}
					return userinfo;
				}

}
